﻿sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'clr enabled', 1;
GO
RECONFIGURE;
GO


IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[JaroWinkler]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[JaroWinkler]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ChapmanLengthDeviation]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[ChapmanLengthDeviation]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ChapmanMeanLength]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[ChapmanMeanLength]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[QGramsDistance]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[QGramsDistance]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[BlockDistance]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[BlockDistance]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[CosineSimilarity]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[CosineSimilarity]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DiceSimilarity]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[DiceSimilarity]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[EuclideanDistance]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[EuclideanDistance]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[JaccardSimilarity]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[JaccardSimilarity]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MatchingCoefficient]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[MatchingCoefficient]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MongeElkan]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[MongeElkan]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[OverlapCoefficient]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[OverlapCoefficient]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetMD5FromString]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[GetMD5FromString]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[FormatDate]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[FormatDate]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Levenstein]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[Levenstein]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[NeedlemanWunch]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[NeedlemanWunch]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SmithWaterman]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[SmithWaterman]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SmithWatermanGotoh]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[SmithWatermanGotoh]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SmithWatermanGotohWindowedAffine]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[SmithWatermanGotohWindowedAffine]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Jaro]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[Jaro]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Concatenate]') AND type = N'AF')
	DROP AGGREGATE [dbo].[Concatenate]
GO


IF  EXISTS (SELECT * FROM sys.assemblies asms WHERE asms.name = N'ESF.MSSQLHelper')
DROP ASSEMBLY [ESF.MSSQLHelper]
GO

IF  EXISTS (SELECT * FROM sys.assemblies asms WHERE asms.name = N'SimMetrics')
DROP ASSEMBLY [SimMetrics]
GO




CREATE ASSEMBLY [ESF.MSSQLHelper]
AUTHORIZATION [dbo]
	FROM 'C:\Lib\MSSQL\ESF.MSSQLHelper.dll' WITH PERMISSION_SET = SAFE 
GO 

CREATE FUNCTION Levenstein(@firstword NVARCHAR(255), @secondword NVARCHAR(255)) 
	RETURNS float EXTERNAL NAME [ESF.MSSQLHelper].[ESF.MSSQLHelper.SimMetrics].Levenstein
GO 

CREATE FUNCTION NeedlemanWunch(@firstword NVARCHAR(255),@secondword NVARCHAR(255)) 
	RETURNS float EXTERNAL NAME [ESF.MSSQLHelper].[ESF.MSSQLHelper.SimMetrics].NeedlemanWunch
GO 

CREATE FUNCTION SmithWaterman(@firstword NVARCHAR(255),@secondword NVARCHAR(255)) 
	RETURNS float EXTERNAL NAME [ESF.MSSQLHelper].[ESF.MSSQLHelper.SimMetrics].SmithWaterman
GO 

CREATE FUNCTION SmithWatermanGotoh(@firstword NVARCHAR(255),@secondword NVARCHAR(255)) 
	RETURNS float EXTERNAL NAME [ESF.MSSQLHelper].[ESF.MSSQLHelper.SimMetrics].SmithWatermanGotoh
GO 

CREATE FUNCTION SmithWatermanGotohWindowedAffine(@firstword NVARCHAR(255),@secondword NVARCHAR(255)) 
	RETURNS float EXTERNAL NAME [ESF.MSSQLHelper].[ESF.MSSQLHelper.SimMetrics].SmithWatermanGotohWindowedAffine
GO 

CREATE FUNCTION Jaro(@firstword NVARCHAR(255),@secondword NVARCHAR(255)) 
	RETURNS float EXTERNAL NAME [ESF.MSSQLHelper].[ESF.MSSQLHelper.SimMetrics].Jaro
GO 

CREATE FUNCTION JaroWinkler(@firstword NVARCHAR(255),@secondword NVARCHAR(255)) 
	RETURNS float EXTERNAL NAME [ESF.MSSQLHelper].[ESF.MSSQLHelper.SimMetrics].JaroWinkler
GO 

CREATE FUNCTION ChapmanLengthDeviation(@firstword NVARCHAR(255),@secondword NVARCHAR(255)) 
	RETURNS float EXTERNAL NAME [ESF.MSSQLHelper].[ESF.MSSQLHelper.SimMetrics].ChapmanLengthDeviation
GO 

CREATE FUNCTION ChapmanMeanLength(@firstword NVARCHAR(255),@secondword NVARCHAR(255)) 
	RETURNS float EXTERNAL NAME [ESF.MSSQLHelper].[ESF.MSSQLHelper.SimMetrics].ChapmanMeanLength
GO 

CREATE FUNCTION QGramsDistance(@firstword NVARCHAR(255),@secondword NVARCHAR(255)) 
	RETURNS float EXTERNAL NAME [ESF.MSSQLHelper].[ESF.MSSQLHelper.SimMetrics].QGramsDistance
GO 

CREATE FUNCTION BlockDistance(@firstword NVARCHAR(255),@secondword NVARCHAR(255)) 
	RETURNS float EXTERNAL NAME [ESF.MSSQLHelper].[ESF.MSSQLHelper.SimMetrics].BlockDistance
GO 

CREATE FUNCTION CosineSimilarity(@firstword NVARCHAR(255),@secondword NVARCHAR(255)) 
	RETURNS float EXTERNAL NAME [ESF.MSSQLHelper].[ESF.MSSQLHelper.SimMetrics].CosineSimilarity
GO 

CREATE FUNCTION DiceSimilarity(@firstword NVARCHAR(255),@secondword NVARCHAR(255)) 
	RETURNS float EXTERNAL NAME [ESF.MSSQLHelper].[ESF.MSSQLHelper.SimMetrics].DiceSimilarity
GO 

CREATE FUNCTION EuclideanDistance(@firstword NVARCHAR(255),@secondword NVARCHAR(255)) 
	RETURNS float EXTERNAL NAME [ESF.MSSQLHelper].[ESF.MSSQLHelper.SimMetrics].EuclideanDistance
GO 

CREATE FUNCTION JaccardSimilarity(@firstword NVARCHAR(255),@secondword NVARCHAR(255)) 
	RETURNS float EXTERNAL NAME [ESF.MSSQLHelper].[ESF.MSSQLHelper.SimMetrics].JaccardSimilarity
GO 

CREATE FUNCTION MatchingCoefficient(@firstword NVARCHAR(255),@secondword NVARCHAR(255)) 
	RETURNS float EXTERNAL NAME [ESF.MSSQLHelper].[ESF.MSSQLHelper.SimMetrics].MatchingCoefficient
GO 

CREATE FUNCTION MongeElkan(@firstword NVARCHAR(255),@secondword NVARCHAR(255)) 
	RETURNS float EXTERNAL NAME [ESF.MSSQLHelper].[ESF.MSSQLHelper.SimMetrics].MongeElkan
GO 

CREATE FUNCTION OverlapCoefficient(@firstword NVARCHAR(255),@secondword NVARCHAR(255)) 
	RETURNS float EXTERNAL NAME [ESF.MSSQLHelper].[ESF.MSSQLHelper.SimMetrics].OverlapCoefficient
GO 

CREATE FUNCTION GetMD5FromString(@pword NVARCHAR(MAX)) 
	RETURNS NVARCHAR(MAX) EXTERNAL NAME [ESF.MSSQLHelper].[ESF.MSSQLHelper.TextHelper].GetMD5FromString
GO 

CREATE AGGREGATE [Concatenate] (@input nvarchar(MAX)) 	
	RETURNS nvarchar(max) EXTERNAL NAME [ESF.MSSQLHelper].[ESF.MSSQLHelper.Concatenate];
GO